The present invention relates to relational database systems, and more specifically, to identifying relevant database indexes in database transactions.
Relational database systems generally store data in sets of tables. Structured Query Language (SQL) queries are issued against the tables to obtain information from the database. SQL is a declarative language that only specifies what data is to be retrieved and does not specify how to retrieve it. The database query optimizer is the component of a database system that determines the strategy for how the data requested by the query should be retrieved, which is called the query execution plan. Database systems typically use a cost-based query optimizer, which determines the possible execution plans, assigns a cost value to each plan, and chooses the one with the least cost. This cost is a measure of the resources, such as CPU, IO and memory required to execute the query using the given plan. The lower the cost, the faster the query would execute.
Database systems use indexes to speed up the processing of queries. Without indexes or other mechanisms to accelerate the retrieval of certain data, the database system would need to scan complete tables to identify the table records that satisfy the predicates of a query. Depending on the table size, this approach could take a very long time. Therefore, one or more indexes are almost always defined on tables. There is usually one primary index that ensures uniqueness of the table records and multiple secondary indexes that are defined according to the queries that are executed on a table.
A downside of indexes is that when data records are inserted, updated or deleted in a table, the affected index trees need to be maintained accordingly, which consumes CPU cycles and potentially requires I/O activity. Therefore, to optimize the performance of SQL INSERT, UPDATE and DELETE statements, it would be best to have as few indexes as possible.
There should be a reasonable trade-off between having many indexes for a good performance of queries and having few indexes for a good performance of insert, update and delete. A database component called an index advisor (also called database design advisor) may be used to provide recommendations for table indexes based on a set of SQL statements on tables.